import json
import os

import mysql.connector

# MySQL 数据库连接配置
config = {
    'user': 'hsh',
    'password': '8198241817',
    'host': 'localhost',
    'database': 'yuntianhui',
    'raise_on_warnings': True
}

Partition={
    "游戏":{
        "id":1,
        "pn":50,
    },
    "动画":{
        "id":2,
        "pn":50,
    },
    "生活":{
        "id":3,
        "pn":50,
    },
    "轻小说":{
        "id":16,
        "pn":50,
    },
    "科技":{
        "id":17,
        "pn":55,
    },
    "影视":{
        "id":28,
        "pn":50,
    },
    "兴趣":{
        "id":29,
        "pn":50,
    },
}



def flatten_json(json_data, parent_key='', sep='_'):
    flat_list = []
    for item in json_data:
        item_dict = {}
        for key, value in item.items():
            # 如果父键为空，则直接使用原始键；否则在原始键前添加父键和分隔符
            new_key = f"{parent_key}{sep}{key}" if parent_key else key
            if isinstance(value, dict):
                nested_dict = flatten_json([value], new_key, sep=sep)
                item_dict.update(nested_dict[0])
            elif isinstance(value, list):
                nested_list = flatten_json(value, new_key, sep=sep)
                for nested_key, nested_value in nested_list.items():
                    item_dict[nested_key] = nested_value
            else:
                item_dict[new_key] = value
        flat_list.append(item_dict)
    return flat_list


def flatten_categories():
    for category, details in Partition.items():
        # with open(f"./data/posts/{category}.json", "r", encoding="utf-8") as f1:
        # with open(f"./data_wyx/6.20/posts/{category}.json", "r", encoding="utf-8") as f1:
        with open(f"./data_wyx/6.21/posts/{category}.json", "r", encoding="utf-8") as f1:
            data = json.load(f1)
            if not os.path.exists('./data_wyx/6.21/posts/flatten_data'):
                os.makedirs('./data_wyx/6.21/posts/flatten_data')
            # with open(f"./data_wyx/6.20/posts/flatten_data/flatten_{category}.json", "w", encoding="utf-8") as f2:
            with open(f"./data_wyx/6.21/posts/flatten_data/flatten_{category}.json", "w", encoding="utf-8") as f2:
                # 平展 JSON 数据
                flat_data = flatten_json(data)
                # 输出平展后的 JSON 数据
                print(flat_data)
                json.dump(flat_data, f2, ensure_ascii=False, indent=4)


def delete_space():
    for category, details in Partition.items():
        # with open("./data/posts/posts_detail.json", "r", encoding="utf-8") as f1:
        # with open(f"./data_wyx/6.20/posts/{category}.json", "r", encoding="utf-8") as f1:
        # with open("./data_wyx/6.20/posts/posts_detail.json", "r", encoding="utf-8") as f1:
        # with open(f"./data_wyx/6.21/posts/{category}.json", "r", encoding="utf-8") as f1:
        with open("./data_wyx/6.21/posts/posts_detail.json", "r", encoding="utf-8") as f1:

            data = json.load(f1)
            for item in data:
                if 'detail ' in item:
                    item['detail'] = item.pop('detail ').strip()
                # item["author"]["mid"] = item["author"].pop("mid ")
                print(item["id"])
            # with open("./data/posts/posts_detail.json", "w", encoding="utf-8") as f2:
            # with open(f"./data_wyx/6.20/posts/{category}.json", "w", encoding="utf-8") as f2:
            # with open("./data_wyx/6.20/posts/posts_detail.json", "w", encoding="utf-8") as f2:
            # with open(f"./data_wyx/6.21/posts/{category}.json", "w", encoding="utf-8") as f2:
            with open("./data_wyx/6.21/posts/posts_detail.json", "w", encoding="utf-8") as f2:

                json.dump(data, f2, ensure_ascii=False, indent=2)

def insert_posts_data(data):
    try:
        db = mysql.connector.connect(**config)
        print("数据库连接状态：", db.is_connected())
        cursor = db.cursor()

        # 创建插入 SQL 语句
        sql = "INSERT INTO posts (pid, uid, title, imgurl, likes, collection, evaluate, browsing,categoryname) " \
              "VALUES (%(id)s, %(author_mid)s, %(title)s, %(banner_url)s,  %(stats_like)s, %(stats_favorite)s, %(stats_reply)s, %(stats_view)s, %(category_name)s)"
        # 执行插入操作

        for record in data:
            try:
                cursor.execute(sql, record)
            except mysql.connector.Error as e:
                print(f"Error inserting record {record}: {e}")
                db.rollback()  # 回滚本次插入，以免影响后续操作
            else:
                db.commit()  # 提交当前成功的插入操作
                print(f"Record {record['id']} inserted successfully.")


    except Exception as e:

        print("插入数据时发生错误：", e)

    finally:

        if db.is_connected():
            db.close()

            print("MySQL 连接已关闭。")

def insert_posts():
    for category, details in Partition.items():
        # with open(f"./data/posts/flatten_data/flatten_{category}.json", "r", encoding="utf-8") as f1:
        # with open(f"./data_wyx/6.20/posts/flatten_data/flatten_{category}.json", "r", encoding="utf-8") as f1:
        with open(f"./data_wyx/6.21/posts/flatten_data/flatten_{category}.json", "r", encoding="utf-8") as f1:
            data = json.load(f1)
            insert_posts_data(data)


def insert_posts_details():
    # with open("./data/posts/posts_detail.json", "r", encoding="utf-8") as f1:
    with open("./data_wyx/6.20/posts/posts_detail.json", "r", encoding="utf-8") as f1:

        data = json.load(f1)
        # for record in data:
        #     id_to_update = record['id']
        #     new_detail = record['detail']
        #     print(id_to_update)

        # 建立数据库连接
        cnx = mysql.connector.connect(**config)
        cursor = cnx.cursor()

        # 目标表名
        table_name = 'posts'

        for record in data:
            try:
                id_to_update = record['id']
                new_detail = record['detail']
                print(id_to_update)
                update_query = f"UPDATE {table_name} SET content = %s WHERE pid = %s"
                cursor.execute(update_query, (new_detail, id_to_update))
                cnx.commit()
                print(f"Record with id {id_to_update} has been updated.")
            except Exception as e:
                print(e)



def insert_users():
    # with open(f"./data/users/users.json", "r", encoding="utf-8") as f1:
    # with open(f"./data_wyx/6.20/users/users.json", "r", encoding="utf-8") as f1:
    with open(f"./data_wyx/6.21/users/users.json", "r", encoding="utf-8") as f1:
        data = json.load(f1)
    try:
        # 建立数据库连接
        db = mysql.connector.connect(**config)
        cursor = db.cursor()

        sql = "INSERT INTO user ( uid, nickname, gender, avatarurl, introduction) " \
              "VALUES (%(mid)s, %(name)s, %(sex)s, %(face)s,  %(sign)s)"

        for record in data:
            try:
                cursor.execute(sql, record)
            except mysql.connector.Error as e:
                print(f"Error inserting record {record}: {e}")
                db.rollback()  # 回滚本次插入，以免影响后续操作
            else:
                db.commit()  # 提交当前成功的插入操作
                print(f"Record {record['mid']} inserted successfully.")


    except Exception as e:
            print("插入数据时发生错误：", e)

    finally:

        if db.is_connected():
            db.close()
            print("MySQL 连接已关闭。")
            # 执行插入操作


def is_same():
    for category, details in Partition.items():
        ids = []
        unique_data = []
        # with open(f"./data/posts/posts_detail.json", "r", encoding="utf-8") as f1:
        # with open(f"./data_wyx/6.20/posts/posts_detail.json", "r", encoding="utf-8") as f1:
        with open(f"./data_wyx/6.21/posts/posts_detail.json", "r", encoding="utf-8") as f1:
            data = json.load(f1)
            for item in data:
                ids.append(item['id'])
        unique_ids = set(ids)
        for item in data:
            current_id = item['id']
            if current_id in unique_ids:
                unique_data.append(item)
                unique_ids.remove(current_id)  # Remove id from set to avoid duplicates
        # with open(f"./data/posts/posts_detail.json", "w", encoding="utf-8") as f2:
        # with open(f"./data_wyx/6.20/posts/posts_detail.json", "w", encoding="utf-8") as f2:
        with open(f"./data_wyx/6.21/posts/posts_detail.json", "w", encoding="utf-8") as f2:
            json.dump(unique_data, f2, ensure_ascii=False, indent=4)


def calculate_data():
    url_list = []
    for category, details in Partition.items():
        with open(f"./data/posts/{category}.json", "r", encoding="utf-8") as f2:
            data = json.load(f2)
            for item in data:
                url_data = {
                    "id": item["id"],
                }
                url_list.append(url_data)
    print(len(url_list))

def flatten_replies(comment, flattened_comments):
    flattened_comments.append(comment)
    if 'replies' in comment:
        for reply in comment['replies']:
            flatten_replies(reply, flattened_comments)

def flatten_comments(data):
    flattened_comments = []
    for comment in data:
        flatten_replies(comment, flattened_comments)
    return flattened_comments

def flatten_replies(data):
    flattened_data = []

    def flatten(item):
        flattened_item = item.copy()
        replies = flattened_item.pop('replies', [])  # 取出 replies 字段并清空原数据的 replies

        # 将当前评论加入结果列表
        flattened_data.append(flattened_item)

        # 递归处理 replies 中的每条回复
        for reply in replies:
            flatten(reply)

    for record in data:
        flatten(record)

    return flattened_data

def flatten_replies(data):
    flattened_data = []

    def flatten(item):
        flattened_item = item.copy()
        replies = flattened_item.pop('replies', [])  # 取出 replies 字段并清空原数据的 replies

        # 将当前评论加入结果列表
        flattened_data.append(flattened_item)

        # 递归处理 replies 中的每条回复
        for reply in replies:
            flatten(reply)

    for record in data:
        flatten(record)

    return flattened_data

def flatten_contents_final():
    def flatten(item):
        flattened_item = {
            "rpid": item["rpid"],
            "oid": item["oid"],
            "mid": item["mid"],
            "member_mid": item["member"]["mid"],
            "member_uname": item["member"]["uname"],
            "member_avatar": item["member"]["avatar"],
            "content_message": item["content"]["message"] if "message" in item["content"] else None,
            "content_members_mid": int(item["content"]["members"][0]["mid"]) if item["content"]["members"] else None,
            "content_members_uname": item["content"]["members"][0]["uname"] if item["content"]["members"] else None,
            "content_members_avatar": item["content"]["members"][0]["avatar"] if item["content"]["members"] else None,
            "reply_control_location": item["reply_control"]["location"] if "reply_control" in item and "location" in item["reply_control"] else None
        }
        return flattened_item

    flattened_data = []
    # with open(f"./data/contents/contents_flatten.json", "r", encoding="utf-8") as f2:
    # with open(f"./data_wyx/6.20/contents/contents_flatten.json", "r", encoding="utf-8") as f2:
    with open(f"./data_wyx/6.21/contents/contents_flatten.json", "r", encoding="utf-8") as f2:

        data = json.load(f2)
    for record in data:
        # print(record)
        for item in record:
            print(item)
            flattened_data.append(flatten(item))

    print(flattened_data)
    # with open("./data/contents/contents_flatten_final.json", "w", encoding="utf-8") as f2:
    # with open("./data_wyx/6.20/contents/contents_flatten_final.json", "w", encoding="utf-8") as f2:
    with open("./data_wyx/6.21/contents/contents_flatten_final.json", "w", encoding="utf-8") as f2:
        json.dump(flattened_data, f2, ensure_ascii=False, indent=4)

def insert_contents():
    # with open("./data/contents/contents_flatten_final.json", "r", encoding="utf-8") as f1:
    # with open("./data_wyx/6.20/contents/contents_flatten_final.json", "r", encoding="utf-8") as f1:
    with open("./data_wyx/6.21/contents/contents_flatten_final.json", "r", encoding="utf-8") as f1:
        data = json.load(f1)

        try:
            db = mysql.connector.connect(**config)
            print("数据库连接状态：", db.is_connected())
            cursor = db.cursor()

            # 创建插入 SQL 语句
            sql = "INSERT INTO evaluate (id, cid, uid, replyuserid, content) " \
                  "VALUES (%(rpid)s, %(oid)s, %(mid)s, %(content_members_mid)s, %(content_message)s)"

            for record in data:
                try:
                    cursor.execute(sql, record)
                except mysql.connector.Error as e:
                    print(f"Error inserting record {record}: {e}")
                    db.rollback()  # 回滚本次插入，以免影响后续操作
                else:
                    db.commit()  # 提交当前成功的插入操作
                    print(f"Record {record['rpid']} inserted successfully.")

        except Exception as e:
            print("插入数据时发生错误：", e)

        finally:
            if db.is_connected():
                db.close()
                print("MySQL 连接已关闭。")
def delete_null():
    # input_file = './data/contents/contents.json'  # 输入的 JSON 文件路径
    # input_file = './data_wyx/6.20/contents/contents.json'  # 输入的 JSON 文件路径
    input_file = './data_wyx/6.21/contents/contents.json'  # 输入的 JSON 文件路径

    # output_file = './data/contents/contents.json'  # 输出的 JSON 文件路径
    # output_file = './data_wyx/6.20/contents/contents.json'  # 输出的 JSON 文件路径
    output_file = './data_wyx/6.21/contents/contents.json'  # 输出的 JSON 文件路径



    with open(input_file, 'r', encoding='utf-8') as f:
        data = json.load(f)

    # 移除顶层列表中为 None 的项
    cleaned_data = [item for item in data if item is not None]

    # 将处理后的数据写回到输出文件
    with open(output_file, 'w', encoding='utf-8') as f:
        json.dump(cleaned_data, f, ensure_ascii=False, indent=2)


if __name__ == '__main__':


    # delete_space()

    # is_same()

    # calculate_data()

    # flatten_categories()

    # 插入posts
    # insert_posts()

    # insert_posts_details()

    # insert_users()

    # delete_null()

    # 展平评论
    # with open(f"./data/contents/contents.json", "r", encoding="utf-8") as f1:
    # with open(f"./data_wyx/6.20/contents/contents.json", "r", encoding="utf-8") as f1:

    # with open(f"./data_wyx/6.21/contents/contents.json", "r", encoding="utf-8") as f1:
    #     data = json.load(f1)
    # data_sum = []
    # for item in data:
    #     print(item[1])
    #     flattened_data = flatten_replies(item[1])
    #     data_sum.append(flattened_data)
    #     print(flattened_data)
    #     # with open(f"./data/contents/contents_flatten.json", "w", encoding="utf-8") as f2:
    #     with open(f"./data_wyx/6.21/contents/contents_flatten.json", "w", encoding="utf-8") as f2:
    #         json.dump(data_sum, f2, ensure_ascii=False, indent=2)

    # 最终展平评论

    # flatten_contents_final()

    insert_contents()